import java.sql.*;     //
public class StudentSystem{
    Connection conn;     //ݿӶ
    Statement stm;       //ִм򵥵SQL
    PreparedStatement pstm;//ִдвSQL
    ResultSet rs;        //ѯ
    StudentSystem()throws Exception{
        //
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
        //ݿ  
        conn=DriverManager.getConnection("jdbc:odbc:StudentSystem","sa","sql"); 
    }
    //ѯʾѧϢ
    void selectAll()throws SQLException{
        stm=conn.createStatement();//Statement
        rs=stm.executeQuery("select * from Students");//ִSQL
        //жǷвѯʾ
        while(rs.next()){
          printAll();
        	System.out.println("------------------------------");
        	}
         closeAll();//رStatementResultSet
    }
    //ָѧŲضѧϢ
    void selectNo(String No)throws SQLException{
        stm=conn.createStatement();
        rs=stm.executeQuery("select * from Students where StudentNo='"+No+"'");
        if(rs.next()){
           printAll();
      	}
        closeAll();
    }
    //ѧϢ
    void insertStudent(Student s)throws SQLException{
        //вpreparedStatement
        pstm=conn.prepareStatement("insert into Students values(?,?,?,?,?,?,?)");
        //Ϊֵ
        setValue(s);
        //ִSQL
        pstm.executeUpdate();
        //رpreparedStatement
        pstm.close();
    }
    //ɾָѧŵѧ
    void deleteNo(String No)throws SQLException{
        pstm=conn.prepareStatement("delete from Students where StudentNo=?");
        pstm.setString(1,No);
        pstm.executeUpdate();
    }
    //޸ָѧŵѧϢ
    void updateStudent(Student s)throws SQLException{
        pstm=conn.prepareStatement("update Students set StudentNo=?,Name=?,Age=?,Sex=?,Address=?,Email=?,Phone=? where StudentNo=?");
        setValue(s);
        pstm.setString(8,s.No);
        pstm.executeUpdate();
        pstm.close();
    }
    //ʾݿе
    private void printAll()throws SQLException{
      	System.out.println("ѧţ"+rs.getString(1));
      	System.out.println(""+rs.getString(2));
      	System.out.println("䣺"+rs.getInt(3));
      	System.out.println("Ա"+rs.getString(4));
      	System.out.println("ַ"+rs.getString(5));
      	System.out.println("Email"+rs.getString(6));
      	System.out.println("绰"+rs.getString(7));
    	}
    //Ϊ趨ֵ
    private void setValue(Student s)throws SQLException{
        pstm.setString(1,s.No);     //pstm.setString("StudentNo",s.No);
        pstm.setString(2,s.Name);   //pstm.setString("Name",s.Name);
        pstm.setInt(3,s.Age);       //pstm.setInt("Age",s.Age);
        pstm.setString(4,s.Sex);    //pstm.setString("Sex",s.Sex);
        pstm.setString(5,s.Address);//pstm.setString("Address",s.Address);
        pstm.setString(6,s.Email);  //pstm.setString("Email",s.Email);
        pstm.setString(7,s.Phone);  //pstm.setString("Phone",s.Phone);
    	}
    //رսStatement
    private void closeAll()throws SQLException{
      	rs.close();
      	stm.close();}
    //ر
    void closeConn()throws SQLException{
    	  conn.close();
    	}
}
